# -*- coding:utf-8 -*-
__author__ = '东方鹗'
__blog__ = 'http://www.os373.cn'

from sqlalchemy import Column, Date, ForeignKey, Integer, String, create_engine
from sqlalchemy.orm import relationship, sessionmaker
from sqlalchemy.dialects.mysql.enumerated import ENUM
from sqlalchemy.ext.declarative import declarative_base


Base = declarative_base()
# metadata = Base.metadata


class Department(Base):  #部门
    __tablename__ = 'departments'

    #部门号
    dept_no = Column(String(4), primary_key=True)
    #部门名
    dept_name = Column(String(40), nullable=False, unique=True)


class DeptEmp(Base): #部门员工
    __tablename__ = 'dept_emp'

    #员工号
    emp_no = Column(ForeignKey('employees.emp_no', ondelete='CASCADE'), primary_key=True, nullable=False, index=True)
    #部门号
    dept_no = Column(ForeignKey('departments.dept_no', ondelete='CASCADE'), primary_key=True, nullable=False, index=True)
    #开始日期
    from_date = Column(Date, nullable=False)
    #结束日期
    to_date = Column(Date, nullable=False)

    #部门
    department = relationship('Department')
    #员工
    employee = relationship('Employee')


class DeptManager(Base): #部门经理
    __tablename__ = 'dept_manager'

    #部门号
    dept_no = Column(ForeignKey('departments.dept_no', ondelete='CASCADE'), primary_key=True, nullable=False, index=True)
    #员工号
    emp_no = Column(ForeignKey('employees.emp_no', ondelete='CASCADE'), primary_key=True, nullable=False, index=True)
    #开始时间
    from_date = Column(Date, nullable=False)
    #结束时间
    to_date = Column(Date, nullable=False)

    #部门
    department = relationship('Department')
    #员工
    employee = relationship('Employee')


class Employee(Base): #员工
    __tablename__ = 'employees'

    #员工号
    emp_no = Column(Integer, primary_key=True)
    #出生日期
    birth_date = Column(Date, nullable=False)
    #姓
    first_name = Column(String(14), nullable=False)
    #名
    last_name = Column(String(16), nullable=False)
    #性别
    gender = Column(ENUM('M', 'F'), nullable=False)
    #入职日期
    hire_date = Column(Date, nullable=False)


class Salary(Base): #薪水
    __tablename__ = 'salaries'

    #员工号
    emp_no = Column(ForeignKey('employees.emp_no', ondelete='CASCADE'), primary_key=True, nullable=False, index=True)
     #薪水
    salary = Column(Integer, nullable=False)
    #开始日期
    from_date = Column(Date, primary_key=True, nullable=False)
    #结束日期
    to_date = Column(Date, nullable=False)

    #员工
    employee = relationship('Employee')


class Title(Base): #头衔
    __tablename__ = 'titles'

    #员工号
    emp_no = Column(ForeignKey('employees.emp_no', ondelete='CASCADE'), primary_key=True, nullable=False, index=True)
    #头衔
    title = Column(String(50), primary_key=True, nullable=False)
    #开始日期
    from_date = Column(Date, primary_key=True, nullable=False)
    #结束日期
    to_date = Column(Date)

    #员工
    employee = relationship('Employee')


# 初始化数据库连接:
engine = create_engine('mysql+pymysql://ousikongjian:{}@125.42.176.217:63306/employees'.format('hawk@#'), echo=True)
# 创建DBSession类型:
DBSession = sessionmaker(bind=engine)
session = DBSession()

